Mel Schwan, Stuart Miller, Justin Howard, Paul Adams¶

Lab One: Visualization and Data Preprocessing¶

CRISP-DM stands for cross-industry process for data mining. It provides a structured approach to planning a data mining project. It is a robust and well-proven methodology.

For Lab One we will use the first two portions of this methodology. We will start by focusing on understanding the objectives and requirements from a business perspective, and then using this knowledge to define the data problem and project plan. Next we will identify data quality issues, discover initial insights, or to detect interesting nuggets of information that might for a hypothesis for analysis.

In future labs we will execute all of the CRISP-DM steps.

Each phase of the process:

1. Business understanding
   1. Assess the Current Situation
      1. Inventory of resources
      2. Requirements, assumptions and constraints
      3. Risks and contingencies
      4. Terminology
      5. Costs and benefits
   2. What are the Desired Outputs
   3. What Questions Are We Trying to Answer?
2. Data Understanding
   1. Initial Data Report
   2. Describe Data
   3. Initial Data Exploration
   4. Verify Data Quality
      1. Missing Data
      2. Outliers
   5. Data Quality Report
3. Data Preparation
   1. Select Your Data
   2. Cleanse the Data
      1. Label Encoding
      2. Drop Unnecessary Columns
      3. Altering Datatypes
      4. Dealing With Zeros
   3. Construct Required Data
   4. Integrate Data

1. Stage One - Determine Business Objectives and Assess the Situation ¶

The first stage of the CRISP-DM process is to understand what you want to accomplish from a business perspective. We will define our objectives and constraints that must be properly balanced. The goal of this stage of the process is to uncover important factors that could influence the outcome of our project.

1.1 Assess the Current Situation¶

This analysis of Home Credit's Default Risk dataset will focus on generating accurate loan default risk probabilities. Predicting loan defaults is essential to the profitability of banks and, given the competitive nature of the loan market, a bank that collects the right data can offer and service more loans. The target variable of the dataset is the binary label, 'TARGET', indicating whether the loan entered into default status or not.

1.1.1. Inventory of resources ¶

List the resources available to the project including:

• Personnel:
  • Mel Schwan
  • Stuart Miller
  • Justin Howard
  • Paul Adams
• Data:
  • 307,511 individual loans
  • application_{train|test}.csv
  • bureau.csv
  • bureau_balance.csv
  • POS_CASH_balance.csv
  • credit_card_balance.csv
  • previous_application.csv
  • installments_payments.csv
  • HomeCredit_columns_description.csv |
• Computing resources:
  • Four personnel computers
• Software:
  • Jupyter Notebook
  • Library Packages
    • Seaborne

1.1.2. Requirements, assumptions and constraints - ¶

• Requirements
  • Perform analysis of a data set: exploring the statistical summaries of the features, visualizing the attributes, and making conclusions from the visualizations and analysis. Follow the CRISP-DM framework in your analysis (you are not performing all of the CRISP-DM outline)
  • Demostrate the teams understanding of Homecredits Business and the data model to best predict default on loans

• Assumptions

  • Outlier assumptions
  • Missing data assumptions
  • Joined dataset assumptions

• Constraits

  • Additional datasets
  • Data

1.1.3.Risks and contingencies ¶

• Imputation of data
• Elimintation of features

1.1.4.Terminology ¶

• A glossary of relevant business terminology
• A glossary of data mining terminology, illustrated with examples relevant to the business problem in question.

1.1.5.Costs and benefits ¶

• Cost

  • Many people struggle to get loans due to insufficient or non-existent credit histories. And, unfortunately, this population is often taken advantage of by untrustworthy lenders.

• Benefits

  • Home Credit strives to broaden financial inclusion for the unbanked population by providing a positive and safe borrowing experience. In order to make sure this underserved population has a positive loan experience, Home Credit makes use of a variety of alternative data--including telco and transactional information--to predict their clients' repayment abilities.

1.2 What are the desired outputs of the project? ¶

Business success criteria

• Reduce default rates to five precent

Data mining success criteria

• Build a data model that has a ROC value of ?

Produce project plan -

• 1.0 Business Understanding
  • Describe the purpose of the data set you selected
  • Describe how you would define and measure the outcomes from the dataset
  • How would you measure the effectiveness of a good prediction algorithm
• 2.0 Data Understanding
  • Describe the meaning and type of data for each attribute in the data file
  • Verify data quality: Explain any missing values, duplicate data, and outliers
  • Give simple, appropriate statistics (range, mode, mean, median, variance, counts, etc.) for the most important attributes and describe what they mean
  • Visualize the most important attributes appropriately (at least 5 attributes)
  • Explore relationships between attributes: Look at the attributes via scatter plots, correlation, cross-tabulation, group-wise averages, etc. as appropriate
  • Identify and explain interesting relationships between features and the class you are trying to predict
  • Are there other features that could be added to the data or created from existing features? Which ones?

1.3 What Questions Are We Trying To Answer? ¶

Use Home Credit current customer data to predict whether a potential client is capable of repayment of the loan requested. During this process we will determine the features that are most influencial in determining this target variable.

2. Stage Two - Data Understanding ¶

The second stage of the CRISP-DM process requires you to acquire the data listed in the project resources. This initial collection includes data loading, if this is necessary for data understanding. For example, if you use a specific tool for data understanding, it makes perfect sense to load your data into this tool. If you acquire multiple data sources then you need to consider how and when you're going to integrate these.

2.1 Initial Data Report .¶

Initial data collection report - List the data sources acquired together with their locations, the methods used to acquire them and any problems encountered. Record problems you encountered and any resolutions achieved. This will help both with future replication of this project and with the execution of similar future projects.

2.2 Describe Data ¶

Data description report - Describe the data that has been acquired including its format, its quantity (for example, the number of records and fields in each table), the identities of the fields and any other surface features which have been discovered. Evaluate whether the data acquired satisfies your requirements.

We will use two of the files from the total dataset.

• application_train.csv: Information provided with each loan application
• bureau.csv: Information regarding clients from the credit bureaus

Loan Application Features¶

There are 122 features and 307511 observations in application_train.csv.

The following features are loan application attributes from application_train.csv. Descriptions, types, and and units are given for each feature.

Engineered Loan Application Features¶

The following features were engineered from the loan application features (from application_train.csv).

The following features were engineered from the bureau features (from bureau.csv).

2.3 Verify Data Quality ¶

Note: All this resolutions to data quality described in this section are implemented in cleaning.py, which is used to load the cleaned dataset.

2.3.1. Missing Data ¶

In addition to incorrect datatypes, another common problem when dealing with real-world data is missing values. These can arise for many reasons and have to be either filled in or removed before we train a machine learning model. First, let’s get a sense of how many missing values are in each column

While we always want to be careful about removing information, if a column has a high percentage of missing values, then it probably will not be useful to our model. The threshold for removing columns should depend on the problem

Analysis of Missing Data in Each Feature¶

Each feature or set of features will be discussed in descending order of the rate of missing values.

Building Features with Missing Values
A large number of the features with values are normlaized infromation about the build where the client lives (such as ENTRANCES_MODE or COMMONAREA_AVG). It is plausible that only some of these features exist in a given client's building. It is also plausible that some of the records are missing (not provided by the client). There does not appear to be an indication if values are missing or not applicable to the client. All of these values have a missing rate above 40%.

Missing values in OWN_CAR_AGE
This feature refers to the age of cars owned by the client. Approximately 66.0% of the values are empty. However, there is also a feature FLAG_OWN_CAR, which indicates that the client owns a car. It is reasonable to expect that values will be missing if the client does not own a car. For clients reporting owning a car, all but 5 car age values are present. For clients reporting not owning a car, no car ages are reported. Since the missing rate is actually very small, these missing values could be imputed.

Missing values in EXT_SOURCE_1, EXT_SOURCE_2, and EXT_SOURCE_3
Since there are zeros in these columns, we expect that the missing values represent lack of the external source these clients. Therefore, we will add an encoding feature EXT_SOURCE_<number>_AV for each of the external source features that represents the presence of external information. These original columns will only be used as an interaction with these encoding features and the missing values will be filled with zero.

• EXT_SOURCE_1 has a missing rate of 56.4%
• EXT_SOURCE_2 has a missing rate of 0.2%
• EXT_SOURCE_3 has a missing rate of 19.8%

Missing values in OCCUPATION_TYPE
There does not appear to be a indication that OCCUPATION_TYPE is systematically missing. We will assume that the client did not provide this information and impute with a new categorical level Unknown. OCCUPATION_TYPE has a missing rate of 31.3%.

Missing values in ORGANIZATION_TYPE
Later in the outliers section, it is shown that the NAs in ORGANIZATION_TYPE are associated with occupations listed as 'Unemployed' or 'Pensioners'. We will take this to mean that these clients are not assocated with any employer and will impute these NAs with 'None'. ORGANIZATION_TYPE has a missing rate of 18%.

Missing values for AMT_REQ_CREDIT_BUREAU Features
These features indicate the number of credit enquiries on the client in a given time period: hour, day, week, month, quarter, and year. There does not appear to be a systematic reason the missing values in these features. We will treat these records as if there are no credit enquires for these records and impute with zero. These features have a missing rate of 13.5%.

Remaining Features with Low Missing Rate
The remaining missing features are missing at rates below 0.5%. Imputation on these features should have only a small impact on the total dataset. We will use standard imputation strategies for these features: imputation of the mode for categorical features and imputation of the median for continuous features. We are imputing with continuous features median rather than mode because the continuous features are skewed and median is not impacted by large values in the features.

2.3.2. Outliers ¶

At this point, we may also want to remove outliers. These can be due to typos in data entry, mistakes in units, or they could be legitimate but extreme values. For this project, we will remove anomalies based on the definition of extreme outliers:

https://www.itl.nist.gov/div898/handbook/prc/section1/prc16.htm

• Below the first quartile − 3 ∗ interquartile range
• Above the third quartile + 3 ∗ interquartile range

High Values in DAYS_EMPLOYED¶

There are a large number of entries for DAYS_EMPLOYED outside the main distribution. These entries are at value 365243 and there are 55374 instances.

These high values for DAYS_EMPLOYED appear to be associated with clients that are "Pensioners" or Unemployed and do not list an employment orgainization (ORGANIZATION_TYPE).

Possible Solution

A possible solution to this data quality issue would be to add a None level to ORGANIZATION_TYPE and only use DAYS_EMPLOYED as an interaction with ORGANIZATION_TYPE.

Data Cleaning Script¶

All the cleaning discussed in the sections above are implemented in cleaning.py. This script contains a function (read_clean_data) to apply the cleaning steps and return the cleaned dataset for work.

Details

• Cleaning
  • Read csv with Pandas (setting correct data types)
  • Drop columns that will not be used
  • Recode NA values that are not listed as np.nan
  • Formattings
  • Encode categorical variables
• Returns
  • DataFrame with cleaned data

Sample Output

2.4 Initial Data Exploration ¶

During this stage you'll address data mining questions using querying, data visualization and reporting techniques. These may include:

• Distribution of key attributes (for example, the target attribute of a prediction task)
• Relationships between pairs or small numbers of attributes
• Results of simple aggregations
• Properties of significant sub-populations
• Simple statistical analyses

These analyses may directly address your data mining goals. They may also contribute to or refine the data description and quality reports, and feed into the transformation and other data preparation steps needed for further analysis.

• Data exploration report - Describe results of your data exploration, including first findings or initial hypothesis and their impact on the remainder of the project. If appropriate you could include graphs and plots here to indicate data characteristics that suggest further examination of interesting data subsets.

We used pandas profiler to explore the data sets range, mode, mean, median, variance, counts, etc. After initial assessment we will focus on the most important attributes and describe what their meanin and add interesting observations we have noted for the dataset. NOTE: Install pandas_profiling into your Conda environment by running "conda install -c conda-forge pandas-profiling"

2.4.1 Distributions ¶

2.4.2 Correlations ¶

Can we derive any correlation from this data-set. Pairplot chart gives us correlations, distributions and regression path Correlogram are awesome for exploratory analysis. It allows to quickly observe the relationship between every variable of your matrix. It is easy to do it with seaborn: just call the pairplot function

Pairplot Documentation cab be found here: https://seaborn.pydata.org/generated/seaborn.pairplot.html

Differencing¶

Differencing Specifically, a new series is constructed where the value at the current time step is calculated as the difference between the original observation and the observation at the previous time step. value(t) = observation(t) - observation(t-1)

2.5 Data Quality Report ¶

List the results of the data quality verification. If quality problems exist, suggest possible solutions. Solutions to data quality problems generally depend heavily on both data and business knowledge.

3. Stage Three - Data Preperation ¶

This is the stage of the project where you decide on the data that you're going to use for analysis. The criteria you might use to make this decision include the relevance of the data to your data mining goals, the quality of the data, and also technical constraints such as limits on data volume or data types. Note that data selection covers selection of attributes (columns) as well as selection of records (rows) in a table.

3.1 Select Your Data ¶

This is the stage of the project where you decide on the data that you're going to use for analysis. The criteria you might use to make this decision include the relevance of the data to your data mining goals, the quality of the data, and also technical constraints such as limits on data volume or data types. Note that data selection covers selection of attributes (columns) as well as selection of records (rows) in a table.

Rationale for inclusion/exclusion - List the data to be included/excluded and the reasons for these decisions.

3.2 Clean The Data ¶

This task involves raise the data quality to the level required by the analysis techniques that you've selected. This may involve selecting clean subsets of the data, the insertion of suitable defaults, or more ambitious techniques such as the estimation of missing data by modelling.

3.2.1 Label Encoding ¶

Label Encoding to turn Categorical values to Integers

An approach to encoding categorical values is to use a technique called label encoding. Label encoding is simply converting each value in a column to a number. For example, the body_style column contains 5 different values. We could choose to encode it like this:

convertible -> 0 hardtop -> 1 hatchback -> 2 sedan -> 3 wagon -> 4 http://pbpython.com/categorical-encoding.html

3.2.2 Drop Unnecessary Columns ¶

Sometimes we may not need certain columns. We can drop to keep only relevent data

3.2.3 Altering Data Types ¶

Sometimes we may need to alter data types. Including to/from object datatypes

3.2.4 Dealing With Zeros ¶

Replacing all the zeros from cols. Note You may not want to do this - add / remove as required

3.2.5 Dealing With Duplicates ¶

Remove duplicate rows. Note You may not want to do this - add / remove as required

3.3 Construct Required Data ¶

This task includes constructive data preparation operations such as the production of derived attributes or entire new records, or transformed values for existing attributes.

Derived attributes - These are new attributes that are constructed from one or more existing attributes in the same record, for example you might use the variables of length and width to calculate a new variable of area.

Generated records - Here you describe the creation of any completely new records. For example you might need to create records for customers who made no purchase during the past year. There was no reason to have such records in the raw data, but for modelling purposes it might make sense to explicitly represent the fact that particular customers made zero purchases.

3.4 Integrate Data ¶

These are methods whereby information is combined from multiple databases, tables or records to create new records or values.

Merged data - Merging tables refers to joining together two or more tables that have different information about the same objects. For example a retail chain might have one table with information about each store’s general characteristics (e.g., floor space, type of mall), another table with summarised sales data (e.g., profit, percent change in sales from previous year), and another with information about the demographics of the surrounding area. Each of these tables contains one record for each store. These tables can be merged together into a new table with one record for each store, combining fields from the source tables.

Aggregations - Aggregations refers to operations in which new values are computed by summarising information from multiple records and/or tables. For example, converting a table of customer purchases where there is one record for each purchase into a new table where there is one record for each customer, with fields such as number of purchases, average purchase amount, percent of orders charged to credit card, percent of items under promotion etc.

Construct Our Primary Data Set¶

Join data

4. Stage Four - Exploratory Data Analysis ¶

5. Stage Four - Modelling ¶

As the first step in modelling, you'll select the actual modelling technique that you'll be using. Although you may have already selected a tool during the business understanding phase, at this stage you'll be selecting the specific modelling technique e.g. decision-tree building with C5.0, or neural network generation with back propagation. If multiple techniques are applied, perform this task separately for each technique.

5.1. Modelling technique ¶

Document the actual modelling technique that is to be used.

Import Models below:

5.2. Modelling assumptions ¶

Many modelling techniques make specific assumptions about the data, for example that all attributes have uniform distributions, no missing values allowed, class attribute must be symbolic etc. Record any assumptions made.

- ¶

5.3. Build Model ¶

Run the modelling tool on the prepared dataset to create one or more models.

Parameter settings - With any modelling tool there are often a large number of parameters that can be adjusted. List the parameters and their chosen values, along with the rationale for the choice of parameter settings.

Models - These are the actual models produced by the modelling tool, not a report on the models.

Model descriptions - Describe the resulting models, report on the interpretation of the models and document any difficulties encountered with their meanings.

5.4. Assess Model ¶

Interpret the models according to your domain knowledge, your data mining success criteria and your desired test design. Judge the success of the application of modelling and discovery techniques technically, then contact business analysts and domain experts later in order to discuss the data mining results in the business context. This task only considers models, whereas the evaluation phase also takes into account all other results that were produced in the course of the project.

At this stage you should rank the models and assess them according to the evaluation criteria. You should take the business objectives and business success criteria into account as far as you can here. In most data mining projects a single technique is applied more than once and data mining results are generated with several different techniques.

Model assessment - Summarise the results of this task, list the qualities of your generated models (e.g.in terms of accuracy) and rank their quality in relation to each other.

Revised parameter settings - According to the model assessment, revise parameter settings and tune them for the next modelling run. Iterate model building and assessment until you strongly believe that you have found the best model(s). Document all such revisions and assessments.